import datetime

from pydantic import EmailStr
from typing import Optional, List
from sqlmodel import Field, SQLModel, Relationship
from sqlalchemy import Column, DateTime, func, ForeignKey, Table



# 用户角色关联表
class UserRole(SQLModel, table=True):
    """用户角色关联表"""
    __tablename__ = "user_role"
    __table_args__ = {"comment": "用户角色关联表"}
    
    id: int = Field(primary_key=True, sa_column_kwargs={"autoincrement": True})
    user_id: int = Field(foreign_key="user.id", description="用户ID")
    role_id: int = Field(foreign_key="role.id", description="角色ID")
    
    # 创建时间
    create_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now()), description="创建时间")
    
    # 关联关系
    user: "User" = Relationship()
    role: "Role" = Relationship()


# 角色权限关联表
class RolePermission(SQLModel, table=True):
    """角色权限关联表"""
    __tablename__ = "role_permission"
    __table_args__ = {"comment": "角色权限关联表"}
    
    id: int = Field(primary_key=True, sa_column_kwargs={"autoincrement": True})
    role_id: int = Field(foreign_key="role.id", description="角色ID")
    permission_id: int = Field(foreign_key="permission.id", description="权限ID")
    
    # 创建时间
    create_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now()), description="创建时间")
    
    # 关联关系
    role: "Role" = Relationship()
    permission: "Permission" = Relationship()


class User(SQLModel, table=True):
    __tablename__ = "user"  # 指定表名为 user
    __table_args__ = {"comment": "用户表"}  # 设置表备注为"用户表"

    # 自增 ID（整数）可以简写为 id: int = Field(primary_key=True)，也可以通过固定参数 "autoincrement": True指定为自增
    # id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    id: int = Field(primary_key=True, sa_column_kwargs={"autoincrement": True})

    name: str = Field(max_length=100, description="名称")
    password: str = Field(max_length=255, description="密码")  # 存储加密后的密码
    phone: Optional[str] = Field(default=None, unique=True, index=True, max_length=11, nullable=True, description="电话")  # 电话和邮件必须有一个
    email: EmailStr = Field(default=None, unique=True, index=True, max_length=255,  nullable=True, description="邮箱")
    birthday: datetime.date = Field(default=None, nullable=True, description="出生日期")

    is_active: bool = Field(default=False, description="用户是否激活，True已登录过, False 注册但未登录过")
    last_login: datetime.datetime = Field(default=None, nullable=True, description="上次登录时间")

    # 创建时间（由数据库自动设置）
    create_time:  datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now()), description="创建时间，自动设置为当前创建的时间")
    # 更新时间（由数据库自动更新）
    update_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now(), onupdate=func.now()), description="更新时间，自动更新为当前时间")
    
    # 权限系统关联
    roles: List["Role"] = Relationship(back_populates="users", link_model=UserRole)


class Role(SQLModel, table=True):
    """角色表"""
    __tablename__ = "role"
    __table_args__ = {"comment": "角色表"}
    
    id: int = Field(primary_key=True, sa_column_kwargs={"autoincrement": True})
    name: str = Field(max_length=50, unique=True, index=True, description="角色名称")
    code: str = Field(max_length=50, unique=True, index=True, description="角色代码")
    description: Optional[str] = Field(default=None, max_length=255, description="角色描述")
    is_active: bool = Field(default=True, description="角色是否启用")
    
    # 创建时间
    create_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now()), description="创建时间")
    # 更新时间
    update_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now(), onupdate=func.now()), description="更新时间")
    
    # 关联关系
    users: List["User"] = Relationship(back_populates="roles", link_model=UserRole)
    permissions: List["Permission"] = Relationship(back_populates="roles", link_model=RolePermission)


class Permission(SQLModel, table=True):
    """权限表"""
    __tablename__ = "permission"
    __table_args__ = {"comment": "权限表"}
    
    id: int = Field(primary_key=True, sa_column_kwargs={"autoincrement": True})
    name: str = Field(max_length=50, unique=True, index=True, description="权限名称")
    code: str = Field(max_length=100, unique=True, index=True, description="权限代码")
    resource: str = Field(max_length=50, description="资源名称")
    action: str = Field(max_length=20, description="操作类型")
    description: Optional[str] = Field(default=None, max_length=255, description="权限描述")
    is_active: bool = Field(default=True, description="权限是否启用")
    
    # 创建时间
    create_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now()), description="创建时间")
    # 更新时间
    update_time: datetime.datetime = Field(sa_column=Column(DateTime(timezone=True), default=func.now(), onupdate=func.now()), description="更新时间")
    
    # 关联关系
    roles: List["Role"] = Relationship(back_populates="permissions", link_model=RolePermission)
